from sqlalchemy import select, func, exc, and_, or_, union_all
from app.model.RiskHistoryModel import RiskHistory
from app.config.db import DBUtils
from app.model.RiskHistoryTempModel import RiskHistoryTemp


class RiskHistoryService:

    def __init__(self):
        self.db_utils = DBUtils()

    def select_all_data(self, session, pageNum, pageSize):
        try:
            list_risk_history = session.query(RiskHistory).order_by(RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset(
                    (pageNum - 1) * pageSize).all()
            total_count = session.query(RiskHistory).count()
            risk_history_list = [item.to_dict() for item in list_risk_history]
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": risk_history_list
            }
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_all_temp_data(self, session, pageNum, pageSize):
        try:
            list_risk_history_temp = session.query(RiskHistoryTemp).order_by(RiskHistoryTemp.CREATE_TIME.desc()).limit(pageSize).offset(
                    (pageNum - 1) * pageSize).all()
            total_count = session.query(RiskHistoryTemp).count()
            risk_history_temp_list = [item.to_dict() for item in list_risk_history_temp]
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": risk_history_temp_list
            }
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_data_by_condition(self, session, company, jylx, pageNum, pageSize):
        try:
            if company is None and jylx is None:
                result = self.select_all_data(session, pageNum, pageSize)
            elif company is not None and jylx is None:
                list_risk_history = session.query(RiskHistory).filter(or_(
                    RiskHistory.CYDW.like(f"%{company}%"),
                    RiskHistory.JYDS.like(f"%{company}%")
                )).order_by(RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistory).filter(or_(
                    RiskHistory.CYDW.like(f"%{company}%"),
                    RiskHistory.JYDS.like(f"%{company}%")
                )).count()
                risk_history_list = [item.to_dict() for item in list_risk_history]
                result = {
                    "pageNum": pageNum,
                    "pageSize": pageSize,
                    "total": total_count,
                    "data": risk_history_list
                }
            elif company is None and jylx is not None:
                list_risk_history = session.query(RiskHistory).filter(RiskHistory.JYLX == jylx).order_by(RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistory).filter(RiskHistory.JYLX == jylx).count()
                risk_history_list = [item.to_dict() for item in list_risk_history]
                result = {
                    "pageNum": pageNum,
                    "pageSize": pageSize,
                    "total": total_count,
                    "data": risk_history_list
                }
            else:
                list_risk_history = session.query(RiskHistory).filter(and_(
                    or_(
                        RiskHistory.CYDW.like(f"%{company}%"),
                        RiskHistory.JYDS.like(f"%{company}%")
                    ),
                    RiskHistory.JYLX == jylx
                )).order_by(
                    RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistory).filter(and_(
                    or_(
                        RiskHistory.CYDW.like(f"%{company}%"),
                        RiskHistory.JYDS.like(f"%{company}%")
                    ),
                    RiskHistory.JYLX == jylx
                )).count()
                risk_history_list = [item.to_dict() for item in list_risk_history]
                result = {
                    "pageNum": pageNum,
                    "pageSize": pageSize,
                    "total": total_count,
                    "data": risk_history_list
                }
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_max_id(self, session):
        try:
            maxid = session.query(func.max(RiskHistory.ID)).scalar()
            return maxid if maxid is not None else 0
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_tpl_max_id(self, session):
        try:
            maxid = session.query(func.max(RiskHistoryTemp.ID)).scalar()
            return maxid if maxid is not None else 0
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_data_by_timeinterval(self, session, pageNum, pageSize, type, start_time, end_time):
        try:
            # 获取条件限制后的数据
            if type == 'year':
                list_risk_history = session.query(RiskHistory).filter(and_(
                    func.substr(RiskHistory.TGSJ, 1, 4) >= start_time,
                    func.substr(RiskHistory.TGSJ, 1, 4) <= end_time
                )).order_by(RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistory).filter(and_(
                    func.substr(RiskHistory.TGSJ, 1, 4) >= start_time,
                    func.substr(RiskHistory.TGSJ, 1, 4) <= end_time
                )).count()
            elif type == 'month':
                formatted_tgsj = func.concat(
                    func.substr(RiskHistory.TGSJ, 1, 4),  # 年份
                    '-',
                    func.lpad(func.replace(func.substr(RiskHistory.TGSJ, 6), '月', ''), 2, '0')  # 月份补0
                )
                list_risk_history = session.query(RiskHistory).filter(
                    and_(
                        formatted_tgsj >= start_time,
                        formatted_tgsj <= end_time
                    )
                ).order_by(RiskHistory.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistory).filter(
                    and_(
                        formatted_tgsj >= start_time,
                        formatted_tgsj <= end_time
                    )
                ).count()
            else:
                list_risk_history = []
                total_count = 0
            if len(list_risk_history) > 0 and total_count > 0:
                risk_history_list = [item.to_dict() for item in list_risk_history]
            else:
                risk_history_list = []
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": risk_history_list
            }
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def select_tpl_data_by_timeinterval(self, session, pageNum, pageSize, type, start_time, end_time):
        try:
            # 获取条件限制后的数据
            if type == 'year':
                list_risk_history_temp = session.query(RiskHistoryTemp).filter(and_(
                    func.substr(RiskHistoryTemp.TGSJ, 1, 4) >= start_time,
                    func.substr(RiskHistoryTemp.TGSJ, 1, 4) <= end_time
                )).order_by(RiskHistoryTemp.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistoryTemp).filter(and_(
                    func.substr(RiskHistoryTemp.TGSJ, 1, 4) >= start_time,
                    func.substr(RiskHistoryTemp.TGSJ, 1, 4) <= end_time
                )).count()
            elif type == 'month':
                formatted_tgsj = func.concat(
                    func.substr(RiskHistoryTemp.TGSJ, 1, 4),  # 年份
                    '-',
                    func.lpad(func.replace(func.substr(RiskHistoryTemp.TGSJ, 6), '月', ''), 2, '0')  # 月份补0
                )
                list_risk_history_temp = session.query(RiskHistoryTemp).filter(
                    and_(
                        formatted_tgsj >= start_time,
                        formatted_tgsj <= end_time
                    )
                ).order_by(RiskHistoryTemp.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(RiskHistoryTemp).filter(
                    and_(
                        formatted_tgsj >= start_time,
                        formatted_tgsj <= end_time
                    )
                ).count()
            else:
                list_risk_history_temp = []
                total_count = 0
            if len(list_risk_history_temp) > 0 and total_count > 0:
                risk_history_temp_list = [item.to_dict() for item in list_risk_history_temp]
            else:
                risk_history_temp_list = []
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": risk_history_temp_list
            }
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return None
        finally:
            self.db_utils.remove_session()

    def insert_data(self, session, risk_history):
        try:
            session.add(risk_history)
            session.commit()
            return 1
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return 0
        finally:
            self.db_utils.remove_session()

    def insert_temp_data(self, session, risk_history_temp):
        try:
            session.add(risk_history_temp)
            session.commit()
            return 1
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return 0
        finally:
            self.db_utils.remove_session()

    def clear_data(self, session):
        try:
            result = session.query(RiskHistoryTemp).delete()
            session.commit()
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return 0
        finally:
            self.db_utils.remove_session()

    def verify_data(self, session, type, start_time, end_time):
        # 获取中间表总数据条数
        total_tpl_count = session.query(RiskHistoryTemp).count()
        # 获取条件限制后的数据条数
        if type == 'year':
            total_count = session.query(RiskHistoryTemp).filter(and_(
                func.substr(RiskHistoryTemp.TGSJ,1,4) >= start_time,
                func.substr(RiskHistoryTemp.TGSJ,1,4) <= end_time
            )).count()
        elif type == 'month':
            # 取出年份和月份，并拼接成 "2025-01" 格式
            formatted_tgsj = func.concat(
                func.substr(RiskHistoryTemp.TGSJ, 1, 4),  # 年份
                '-',
                func.lpad(func.replace(func.substr(RiskHistoryTemp.TGSJ, 6), '月', ''), 2, '0')  # 月份补0
            )
            total_count = session.query(RiskHistoryTemp).filter(
                and_(
                    formatted_tgsj >= start_time,
                    formatted_tgsj <= end_time
                )
            ).count()
        else:
            total_count = session.query(RiskHistoryTemp).filter()

    def delete_all_data(self, session, ids):
        try:
            result = session.query(RiskHistory).filter(RiskHistory.ID.in_(ids)).delete()
            session.commit()
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return 0
        finally:
            self.db_utils.remove_session()

    def delete_all_temp_data(self, session, ids):
        try:
            result = session.query(RiskHistoryTemp).filter(RiskHistoryTemp.ID.in_(ids)).delete()
            session.commit()
            return result
        except Exception as e:
            print(e)
            self.db_utils.rollback()
            return 0
        finally:
            self.db_utils.remove_session()


